package com.app.business.service.uni;

import com.app.business.en.Status;
import com.app.business.model.CarLot;
import com.app.business.model.Contract;
import com.app.business.model.House;
import com.app.business.model.StorageRoom;
import com.app.system.jpa.JpaBaseDao;
import com.app.system.utils.CollectionUtils;
import com.google.common.collect.Maps;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Service;

import javax.persistence.Query;
import java.util.*;

/**
 * 手机端
 * 房源业务层
 *
 * @author lsh
 */
@SuppressWarnings(value = "ALL")
@Service
public class AppHouseService extends JpaBaseDao<House, Integer> {
    /**
     * 通过安置项目id获取下面对应的楼号
     *
     * @param
     * @return
     */
    public List<String> getBuildNumListByAzProjectId(String azProjectStr, List houseIdList, Integer yxStatus, Integer kxStatus) {
        StringBuffer sql = new StringBuffer("select distinct h.build_num as buildNum from house h ");
        sql.append(" where h.id in (:houseIdList) and h.az_project_id in " + azProjectStr);
        if (yxStatus == Status.YES.getIndex() && kxStatus == Status.NO.getIndex()) {
            sql.append(" and h.house_status = " + Status.NO.getIndex() + " ");//展示已选
        }
        if (yxStatus == Status.NO.getIndex() && kxStatus == Status.YES.getIndex()) {
            sql.append(" and h.house_status != " + Status.NO.getIndex() + " ");
        }
        sql.append(" order by CONVERT(SUBSTRING_INDEX(h.build_num,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.build_num,'-',-1),SIGNED) ");//展示可选
        List<String> buildNumList = (List<String>) this.entityManager.createNativeQuery(sql.toString()).setParameter("houseIdList", houseIdList).getResultList();
        return buildNumList;
    }

    /**
     * 通过安置项目Id 楼号 楼层 得到对应的房屋信息
     *
     * @param azProjectId
     * @param buildNum
     * @param floor
     * @return
     */
    public List<House> houseList(String azProjectIdStr, String buildNum, Integer floor, List houseIdList, Integer yxStatus, Integer kxStatus) {
        StringBuffer hql = new StringBuffer("from House h where h.azProject.id in " + azProjectIdStr);
        hql.append(" and h.buildNum = :buildNum and h.floor = :floor and h.id in (:houseIdList) ");
        if (yxStatus == Status.YES.getIndex() && kxStatus == Status.NO.getIndex()) {
            hql.append(" and h.houseStatus = " + Status.NO.getIndex() + " ");//展示已选
        }
        if (yxStatus == Status.NO.getIndex() && kxStatus == Status.YES.getIndex()) {
            hql.append(" and h.houseStatus != " + Status.NO.getIndex() + " ");
        }
        hql.append("ORDER by h.houseNum,h.id ");
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("buildNum", buildNum);
        map.put("floor", floor);
        map.put("houseIdList", houseIdList);
        return this.findHql(hql.toString(), map);
    }

    /**
     * 得到楼层列表
     *
     * @param id
     * @param buildNum
     * @return
     */
    public List floorList(String azProjectIdStr, String buildNum, Integer yxStatus, Integer kxStatus) {
        StringBuffer sql = new StringBuffer("select floor from house h where az_project_id in " + azProjectIdStr);
        sql.append(" and build_num = :buildNum ");
        if (yxStatus == Status.YES.getIndex() && kxStatus == Status.NO.getIndex()) {
            sql.append(" and h.house_status = " + Status.NO.getIndex() + " ");//展示已选
        }
        if (yxStatus == Status.NO.getIndex() && kxStatus == Status.YES.getIndex()) {
            sql.append(" and h.house_status != " + Status.NO.getIndex() + " ");
        }
        sql.append("group by floor order by floor desc ");
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("buildNum", buildNum);
        List list = this.findSqlObject(sql.toString(), map);
        return list;
    }


    /**
     * 根据安置项目ID得到该安置区选房情况
     *
     * @return
     */
    public Map getHouseStatisticsByAzProjectId(String azProjectIdStr) {

        StringBuffer sql = new StringBuffer("SELECT ");

        sql.append(" IFNULL(COUNT(DISTINCT(h.id)),0) AS houseAllNum, ");
        sql.append(" SUM(IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) AS houseAllArea, ");

        sql.append(" SUM(CASE WHEN h.house_status  != 1  THEN 1 ELSE 0 END) AS chooseNum, ");
        sql.append(" SUM(CASE WHEN h.house_status  != 1  THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS chooseArea, ");

        sql.append(" SUM(CASE WHEN h.house_status  = 1   THEN 1 ELSE 0 END) AS unChooseNum, ");
        sql.append(" SUM(CASE WHEN h.house_status  = 1   THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS unChooseArea  ");


        sql.append(" FROM house h  ");
        sql.append(" WHERE  h.az_project_id in " + azProjectIdStr);
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map resultMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            resultMap = mapList.get(0);
        }
        return resultMap;
    }


    /**
     * 根据安置项目ID得到该安置区车位选房情况
     *
     * @return
     */
    public Map getCarLotStatisticsByAzProjectId(String azProjectIdStr) {

        StringBuffer sql = new StringBuffer("SELECT ");

        sql.append(" IFNULL(COUNT(DISTINCT(cl.id)),0) AS carLotAllNum, ");

        sql.append(" SUM(CASE WHEN cl.house_status  != 1 AND cl.car_lot_type in(1,2,3)  THEN 1 ELSE 0 END) AS choosePtCarLotNum, ");
        sql.append(" SUM(CASE WHEN cl.house_status  != 1 AND cl.car_lot_type in(4,5,6)  THEN 1 ELSE 0 END) AS chooseRfCarLotNum, ");

        sql.append(" SUM(CASE WHEN cl.house_status  = 1 AND cl.car_lot_type in(1,2,3)  THEN 1 ELSE 0 END) AS unChoosePtCarLotNum, ");
        sql.append(" SUM(CASE WHEN cl.house_status  = 1 AND cl.car_lot_type in(4,5,6)  THEN 1 ELSE 0 END) AS unChooseRfCarLotNum ");

        sql.append(" FROM car_Lot cl  ");
        sql.append(" WHERE  cl.az_project_id in " + azProjectIdStr);
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map resultMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            resultMap = mapList.get(0);
        }
        return resultMap;
    }

    /**
     * 根据房屋户型得到安置区各种户型已选未选数量
     * 根据房屋户型group
     *
     * @return
     */
    public Map<String, Map> getHouseHoldStatisticsByAzProjectId(String azProjectIdStr) {

        StringBuffer sql = new StringBuffer("SELECT h.house_hold as houseHold,");
        sql.append(" IFNULL(COUNT(DISTINCT(h.id)),0) AS houseNum,");
        sql.append(" SUM(CASE WHEN h.house_status  != 1  THEN 1 ELSE 0 END) AS chooseNum, ");
        sql.append(" SUM(CASE WHEN h.house_status  = 1   THEN 1 ELSE 0 END) AS unChooseNum, ");
        sql.append(" SUM(IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) AS houseArea ");
        sql.append(" FROM house h inner join az_project azp ON h.az_project_id = azp.id ");
        sql.append(" WHERE  h.az_project_id in " + azProjectIdStr);
        sql.append(" GROUP BY h.house_hold ORDER BY h.house_hold");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> houseNumMap = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String houseHold = map.get("houseHold").toString();
                houseNumMap.put(houseHold, map);
            }
        }
        return houseNumMap;
    }

    public Map<String, Map> getHouseHoldMap(String azProjectIdStr) {
        Map<String, Map> resultMap = Maps.newLinkedHashMap();
        StringBuffer sql = new StringBuffer("select h.az_project_id as azProjectId,CONCAT(h.build_num,'-',h.house_hold) as buildAndType,h.build_num as buildNum,h.house_hold as houseHold,");
        sql.append("ifnull(count(h.id),0) as houseTotal,sum(ifnull(h.property_area,0)+ifnull(h.pool_area,0)) as houseAreaTotal,");
        sql.append("sum(case when h.house_status !=1 then 1 else 0 end) as chooseHouseTotal,");
        sql.append("sum(case when h.house_status !=1 then (ifnull(h.property_area,0)+ifnull(h.pool_area,0)) else 0 end) as chooseHouseAreaTotal,");
        sql.append("sum(case when h.house_status =1 then 1 else 0 end) as unChooseHouseTotal,");
        sql.append("sum(case when h.house_status =1 then (ifnull(h.property_area,0)+ifnull(h.pool_area,0)) else 0 end) as unChooseHouseAreaTotal ");
        sql.append("from house h where h.az_project_id in " + azProjectIdStr + "   ");
        sql.append("GROUP BY buildAndType ");
        sql.append("ORDER BY CONVERT(SUBSTRING_INDEX(h.build_num,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.build_num,'-',-1),SIGNED),h.house_hold ");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String buildAndType = map.get("buildAndType").toString();
                resultMap.put(buildAndType, map);
            }
        }
        return resultMap;
    }

    /**
     * 通过征收项目projectIdList 得到分配的houseId
     *
     * @param zsProjectId
     * @return
     */
    public List<Integer> getHouseIdList(String zsProjectId) {
        String sql = "select zph.house_id from zs_project_house zph where zph.zs_project_id in " + zsProjectId;
        Map<String, Object> param = Maps.newHashMap();
        return (List<Integer>) this.findSqlObject(sql, param);
    }

    /**
     * 得到协议id不为空的房源map
     *
     * @return
     */
    public Map<String, List<House>> getHouseByContractIdMap(List<Integer> houseIdList, String azProjectIdStr) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id is not null ");
        hql.append(" and h.id in :houseIdList and azp.id in " + azProjectIdStr);
        hql.append(" order by h.chooseHouseDate");
        List<House> houseList = entityManager.createQuery(hql.toString())
                .setParameter("houseIdList", houseIdList)
                .getResultList();

        List<House> valueList = null;

        Map<String, List<House>> map = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(houseList)) {
            for (House h : houseList) {
                String id = String.valueOf(h.getContract().getId());
                valueList = map.get(id) == null ? new ArrayList<>() : map.get(id);
                valueList.add(h);
                map.put(id, valueList);
            }
        }
        return map;
    }

    /**
     * 得到协议id不为空的车位map
     *
     * @return
     */
    public Map<String, List<CarLot>> getCarLotByContractIdMap(String azProjectIdStr) {
        StringBuffer hql = new StringBuffer("from CarLot c inner join fetch c.azProject azp where c.contract.id is not null ");
        hql.append(" and azp.id in  " + azProjectIdStr);
        hql.append(" order by c.chooseHouseDate");
        List<CarLot> carLotList = entityManager.createQuery(hql.toString()).getResultList();

        List<CarLot> valueList = null;

        Map<String, List<CarLot>> map = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(carLotList)) {
            for (CarLot c : carLotList) {
                String id = String.valueOf(c.getContract().getId());
                valueList = map.get(id) == null ? new ArrayList<>() : map.get(id);
                valueList.add(c);
                map.put(id, valueList);
            }
        }
        return map;
    }

    /**
     * 得到协议id不为空的储物间map
     *
     * @return
     */
    public Map<String, List<StorageRoom>> getStorageRoomByContractIdMap(String azProjectIdStr) {
        StringBuffer hql = new StringBuffer("from StorageRoom s inner join fetch s.azProject azp where s.contract.id is not null ");
        hql.append(" and azp.id in  " + azProjectIdStr);
        hql.append(" order by s.chooseHouseDate ");

        List<StorageRoom> storageRoomList = entityManager.createQuery(hql.toString()).getResultList();

        List<StorageRoom> valueList = null;

        Map<String, List<StorageRoom>> map = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(storageRoomList)) {
            for (StorageRoom s : storageRoomList) {
                String id = String.valueOf(s.getContract().getId());
                valueList = map.get(id) == null ? new ArrayList<>() : map.get(id);
                valueList.add(s);
                map.put(id, valueList);
            }
        }
        return map;
    }

    /**
     * 通过houseIdList 得到对应的HouseList
     * @param houseIdList
     * @return
     */
    public List<House> houseListByIdList(List<Integer> houseIdList) {
        houseIdList.add(0);
        StringBuffer hql = new StringBuffer("from House h  where h.id in (:houseIdList) order by h.propertyArea + h.poolArea  ");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("houseIdList", houseIdList).getResultList();
        return houseList;
    }

    /**
     * 备选户型list
     * @param contractId
     * @return
     */
    public List<String> getHouseHoldByIdList(List<Integer> houseIdList) {
        houseIdList.add(0);
        Map<String, Object> map = Maps.newLinkedHashMap();
        StringBuffer sql = new StringBuffer("select distinct h.house_hold ");
        sql.append("from house h  where h.id in (:houseIdList) order by h.house_hold ");
        map.put("houseIdList", houseIdList);
        return (List<String>) this.findSqlObject(sql.toString(), map);
    }


    /**
     * 备选户型对应房源信息
     * @param houseIdList 备选houseId
     * @param houseStatusMap 手机端房源状态
     * @param contract 协议
     * @param noChooseHouseId 不可选houseId
     * @return
     */
    public Map<String, List<Map<String,Object>>> getBxHouseByHold(List<Integer> houseIdList,Map houseStatusMap,Contract contract,List<Integer> noChooseHouseId) {
        Map<String, List<Map<String,Object>>> resultMap = Maps.newHashMap();
        StringBuffer hql = new StringBuffer("from House h  where h.id in (:houseIdList) order by h.propertyArea + h.poolArea  ");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("houseIdList", houseIdList).getResultList();
        if (CollectionUtils.isNotEmpty(houseList)) {

            for (House house : houseList) {
                List<Map<String,Object>> itemList = new LinkedList<>();
                Map<String,Object> itemMap = new HashMap<>();//封装所需数据
                itemMap.put("houseId",house.getId());
                itemMap.put("buildNum",house.getBuildNum());
                itemMap.put("houseNum",house.getHouseNum());
                itemMap.put("buildArea",house.getBuildArea());
                itemMap.put("houseHold",house.getHouseHold());
                //不可选状态设置
                Integer houseStatus = house.getHouseStatus();
                if (contract.getChooseLimitType() != 1) {//有限制 状态做处理
                    if (noChooseHouseId == null && house.getHouseStatus() == 1) {//若是空都不可选
                        houseStatus = 3;
                    } else if (noChooseHouseId != null && noChooseHouseId.contains(house.getId()) && house.getHouseStatus() == 1) {//已选房屋颜色优先级 高于 不可选优先级
                        houseStatus = 3;
                    }
                }
                itemMap.put("houseStatus",houseStatus);
                itemMap.put("style", "background:" + houseStatusMap.get(String.valueOf(houseStatus)));
                String hold = house.getHouseHold();
                if (resultMap != null && resultMap.get(hold) != null){//resultMap 中已存在对应的房源信息
                    itemList = resultMap.get(hold);
                    itemList.add(itemMap);
                    resultMap.put(hold,itemList);
                } else {
                    itemList.add(itemMap);
                    resultMap.put(hold,itemList);
                }
            }
        }
        return resultMap;
    }

    /**
     *预选房源数量
     * @param contractId
     * @return
     */
    public Map<String, Integer> getYxHouseNum(String chooseHouseNum) {
        Map<String, Integer> resultMap = Maps.newHashMap();
        StringBuffer sql = new StringBuffer("SELECT yx.house_id AS houseId,COUNT(DISTINCT yx.contract_id) AS yxNum ");
        sql.append("FROM yx_house yx,contract c  ");
        sql.append("WHERE yx.contract_id = c.id  ");
        sql.append("AND c.choose_house_num <" + chooseHouseNum );
        sql.append(" GROUP BY yx.house_id ");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String houseId = map.get("houseId").toString();
                Integer yxNum = Integer.valueOf(map.get("yxNum").toString());
                resultMap.put(houseId,yxNum);
            }
        }
        return resultMap;
    }

}
